using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop;
using System.Data.Entity;
using MigraDoc.DocumentObjectModel;
using MigraDoc.Rendering;
using <Application>.<Business>;

namespace <Application>.<Data>
{
    public class <Database>DBContext : DbContext
    {
        public <Database>DBContext()
            : base("<Database>DBContext")
        {
            Database.SetInitializer<<Database>DBContext>(null);
            Configuration.ProxyCreationEnabled = false;
        }

    <RTable Loop>
        public DbSet<<OwnerU>_<TableU>> <OwnerU>_<TableU> { get; set; }

    </RTable Loop>
    }

    public class <Database>DataClass
    {
        public static void ExportToExcel(string Type, string Head, System.Windows.Forms.DataGridView Grid)
        {
            try
            {
                if (Grid.ColumnCount > 0)
                {
                    Microsoft.Office.Interop.Excel.Application oExcel;
                    Microsoft.Office.Interop.Excel.Workbook oBook;
                    Microsoft.Office.Interop.Excel.Worksheet oSheet;

                    oExcel = new Microsoft.Office.Interop.Excel.Application();
                    oExcel.Visible = false;

                    oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(System.Type.Missing));
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.ActiveSheet;

                    oSheet.Range["B2"].Value = Head;
                    oSheet.Range["B2"].Font.Bold = true;

                    if (Type == "One")
                    {
                        for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                        {
                            oSheet.Range["B" + (4 + s)].Value = Grid.Columns[s].HeaderText;
                            oSheet.Range["C" + (4 + s)].Value = Grid.SelectedRows[0].Cells[s].Value;
                        }

                        oSheet.Columns["B:B"].ColumnWidth = 20;
                        oSheet.Columns.AutoFit();

                    }
                    else if (Type == "Many")
                    {
                        object[,] DataArrayHead = new object[1, Grid.ColumnCount];
                        for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                        {
                            DataArrayHead[0, s] = Grid.Columns[s].HeaderText;
                        }
    
                        oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Value = DataArrayHead;
                        oSheet.Range["B4"].Resize[1, Grid.ColumnCount].Font.Bold = true;
    
                        object[,] DataArray = new object[Grid.RowCount, Grid.ColumnCount];
                        for (int r = 0; r <= Grid.RowCount - 1; r++)
                        {
                            for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                            {
                                DataArray[r, s] = Grid[s, r].Value;
                            }
                        }
    
                        oSheet.Range["B5"].Resize[Grid.RowCount, Grid.ColumnCount].Value = DataArray;
                        oSheet.Columns.AutoFit();
    
                    }
    
                    oExcel.Visible = true;
                    oExcel = null;
                    oSheet = null;
                }
            }
            catch
            {
            }
        }

        public static void ExportToWord(string Type, string Head, System.Windows.Forms.DataGridView Grid)
        {
            try
            {
                if (Grid.ColumnCount > 0)
                {
                    Microsoft.Office.Interop.Word.Application oWord;
                    Microsoft.Office.Interop.Word.Document oDoc;
                    Microsoft.Office.Interop.Word.Table oTable;
                    Microsoft.Office.Interop.Word.Paragraph oPara1;
    
                    oWord = new Microsoft.Office.Interop.Word.Application();
                    oWord.Visible = false;
                    oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
                    var _with1 = oDoc.PageSetup;
                    _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
                    _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
                    _with1.LeftMargin = oWord.CentimetersToPoints(1);
                    _with1.BottomMargin = oWord.CentimetersToPoints(1);
                    _with1.TopMargin = oWord.CentimetersToPoints(1);

                    oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
                    oPara1.Range.Text = Head;
                    oPara1.Range.Font.Bold = 0;
                    oPara1.Range.Font.Size = 8;
                    oPara1.Format.SpaceAfter = 8;
                    oPara1.Range.InsertParagraphAfter();


                    if (Type == "One")
                    {
                        oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
                        oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
                        for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                        {
                            oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                            oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
                        }
    
                        oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                        oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
                        oTable.AllowAutoFit = true;
                        oTable.Columns.AutoFit();
    
                    }
                    else if (Type == "Many")
                    {
                        oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
                        oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
                        for (int i = 0; i <= Grid.ColumnCount - 1; i++)
                        {
                            oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
                        }

                        for (int r = 0; r <= Grid.RowCount - 1; r++)
                        {
                            for (int s = 0; s <= Grid.ColumnCount - 1; s++)
                            {
                                oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
                            }
                        }

                        oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

                        oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                        oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
                        oTable.AllowAutoFit = true;
                        oTable.Columns.AutoFit();
    
                    }
    
                    oWord.Visible = true;
                    oWord.NormalTemplate.Saved = true;
                    oWord = null;
                    oDoc = null;
                }
            }
            catch
            {
            }
        }

        //public static void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid)
        //{
        //    try
        //    {
        //        if (Grid.ColumnCount > 0)
        //        {
        //            Microsoft.Office.Interop.Word.Application oWord;
        //            Microsoft.Office.Interop.Word.Document oDoc;
        //            Microsoft.Office.Interop.Word.Table oTable;
        //            Microsoft.Office.Interop.Word.Paragraph oPara1;
    
        //            oWord = new Microsoft.Office.Interop.Word.Application();
        //            oWord.Visible = false;
        //            oDoc = (Microsoft.Office.Interop.Word.Document)(oWord.Documents.Add(System.Type.Missing));
        //            var _with1 = oDoc.PageSetup;
        //            _with1.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
        //            _with1.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
        //            _with1.LeftMargin = oWord.CentimetersToPoints(1);
        //            _with1.BottomMargin = oWord.CentimetersToPoints(1);
        //            _with1.TopMargin = oWord.CentimetersToPoints(1);

        //            oPara1 = (Microsoft.Office.Interop.Word.Paragraph)(oDoc.Content.Paragraphs.Add(System.Type.Missing));
        //            oPara1.Range.Text = Head;
        //            oPara1.Range.Font.Bold = 0;
        //            oPara1.Range.Font.Size = 8;
        //            oPara1.Format.SpaceAfter = 8;
        //            oPara1.Range.InsertParagraphAfter();


        //            if (Type == "One")
        //            {
        //                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.ColumnCount, 2);
        //                oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
        //                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
        //                {
        //                    oTable.Cell(i + 1, 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
        //                    oTable.Cell(i + 1, 2).Range.Text = Convert.ToString(Grid.SelectedRows[0].Cells[i].Value);
        //                }
    
        //                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
        //                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
        //                oTable.AllowAutoFit = true;
        //                oTable.Columns.AutoFit();
    
        //            }
        //            else if (Type == "Many")
        //            {
        //                oTable = oDoc.Tables.Add(oDoc.Bookmarks.get_Item("\\endofdoc").Range, Grid.RowCount + 1, Grid.ColumnCount);
        //                oTable.Range.ParagraphFormat.SpaceAfter = 2;
    
        //                for (int i = 0; i <= Grid.ColumnCount - 1; i++)
        //                {
        //                    oTable.Cell(1, i + 1).Range.InsertAfter(Grid.Columns[i].HeaderText);
        //                }

        //                for (int r = 0; r <= Grid.RowCount - 1; r++)
        //                {
        //                    for (int s = 0; s <= Grid.ColumnCount - 1; s++)
        //                    {
        //                        oTable.Cell(r + 2, s + 1).Range.Text = Convert.ToString(Grid[s, r].Value);
        //                    }
        //                }

        //                oTable.Rows[1].Cells.Shading.BackgroundPatternColorIndex = Microsoft.Office.Interop.Word.WdColorIndex.wdGray25;

        //                oTable.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
        //                oTable.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDouble;
    
        //                oTable.AllowAutoFit = true;
        //                oTable.Columns.AutoFit();
    
        //            }

        //            string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
        //            oDoc.ExportAsFixedFormat(FilePath, Microsoft.Office.Interop.Word.WdExportFormat.wdExportFormatPDF, true);
        //            ((Microsoft.Office.Interop.Word._Document)(oDoc)).Close(Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges);
        //            ((Microsoft.Office.Interop.Word._Application)(oWord)).Quit(); 
        //        }
        //    }
        //    catch
        //    {
        //    }
        //}

        public static void ExportToPDF(string Type, string Head, System.Windows.Forms.DataGridView Grid, System.Windows.Forms.DataGridViewRow GridRow)
        {
            try
            {
                DataTable dt = default(DataTable);
                if (Type == "One")
                {
                    dt = DataGridViewRow_To_DataTable(Grid, GridRow, Head);
                }
                else
                {
                    dt = DataGridView_To_DataTable(Grid, Head);
                }
                PDFform pdfForm = new PDFform(dt, Head, Type);

                Document document = pdfForm.CreateDocument();
                document.UseCmykColor = true;
                PdfDocumentRenderer pdfRenderer = new PdfDocumentRenderer(true);
                pdfRenderer.Document = document;
                pdfRenderer.RenderDocument();

                string FilePath = System.IO.Path.GetTempPath() + "\\" + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".pdf";
                pdfRenderer.Save(FilePath);
                System.Diagnostics.Process.Start(FilePath);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadLine();
            }
        }

        //Converts the DataGridView to DataTable
        public static DataTable DataGridView_To_DataTable(System.Windows.Forms.DataGridView dgv, String tblName, int minRow = 0)
        {
            DataTable dt = new DataTable(tblName);

            // Header columns
            foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
            {
                DataColumn dc = new DataColumn(column.Name.ToString());
                dt.Columns.Add(dc);
            }

            // Data cells
            for (int i = 0; i <= dgv.Rows.Count - 1; i++)
            {
                System.Windows.Forms.DataGridViewRow row = dgv.Rows[i];
                DataRow dr = dt.NewRow();
                for (int j = 0; j <= dgv.Columns.Count - 1; j++)
                {
                    dr[j] = (row.Cells[j].Value == null) ? "" : row.Cells[j].Value.ToString();
                }
                dt.Rows.Add(dr);
            }

            // Related to the bug arround min size when using ExcelLibrary for export
            for (int i = dgv.Rows.Count; i <= minRow - 1; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    dr[j] = "  ";
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        //Converts the DataGridViewRow to DataTable
        public static DataTable DataGridViewRow_To_DataTable(System.Windows.Forms.DataGridView dgv, System.Windows.Forms.DataGridViewRow dgvr, String tblName, int minRow = 0)
        {
            DataTable dt = new DataTable(tblName);

            // Header columns
            DataColumn dc1 = new DataColumn("Column");
            dt.Columns.Add(dc1);
            DataColumn dc2 = new DataColumn("Value");
            dt.Columns.Add(dc2);

            foreach (System.Windows.Forms.DataGridViewColumn column in dgv.Columns)
            {
                dt.Rows.Add(dgv.Columns[column.Index].Name.ToString(), dgvr.Cells[column.Index].Value);
            }
            return dt;
        }
    }
}
